Using Mata*Inside Stata Program Loop

[Nick Mahoney]

Hello,

I am attempting to write a Stata/Mata program which formats excel files using Mata, by looping over all .xlsx files in a specified directory. The first problem I have run in to is that Stata gives error message 199 "} is not a valid command name" when it reaches the end of the loop, after the "end" command for mata is processed. I'm assuming this is because Stata thinks "end" should close the program, however, I'm not sure what the solution is. The code is currently set up as follows:

prog formatxl
    args directory

    local list : dir "$root/`directory'" files "*.xlsx" 
    foreach xlfile of local list {
        mata
        b = xl()
        b.load_book("`xlfile'")
        *formatting commands follow
        end
        }
        
end

I know Mata also doesn't work well with local macros, so if that will become a problem I will also need to figure out an alternative way of specifying the files to be formatted, and any advice on this task would be greatly appreciated as well.

Thanks so much,

Nick Mahoney

[William Lisowski]

You will likely find post #2 in the following topic helpful.

https://www.statalist.org/forums/for...-don-t-press-q

[Bjarte Aagnes]

Also:

prog formatxl

    args directory

    local end_mata end
    
    local list : dir "$root/`directory'" files "*.xlsx" 
    
    foreach xlfile of local list {
        
        mata
        
            b = xl()
            b.load_book("`xlfile'")
        
            *formatting commands follow
        
        `end_mata'
        
    }

end

[Nick Mahoney]

Bjarte Aagnes That's brilliant. Thank you!